package com.xiaowei.gym.gymjsp.ware.dao.impl;

import com.mysql.cj.util.StringUtils;
import com.xiaowei.gym.gymjsp.ware.dao.PersonnelDao;
import com.xiaowei.gym.gymjsp.ware.entity.PersonnelEntity;
import com.xiaowei.gym.gymjsp.ware.entity.SalaryEntity;
import com.xiaowei.gym.gymjsp.ware.util.JdbcUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class PersonnelDaoImpl implements PersonnelDao {
    @Override
    public List<PersonnelEntity> getPersonnelAll() {
        String sql = "SELECT * FROM `gym_personnel`";
        List<PersonnelEntity> personnelEntities = null;
        try {
           personnelEntities = JdbcUtil.executeQuery(sql, (JdbcUtil.ResultSetHandler<List<PersonnelEntity>>) rs -> {
                List<PersonnelEntity> personnelEntityList = new ArrayList<>();
                while (rs.next()) {
                    PersonnelEntity personnelEntity = new PersonnelEntity();
                    personnelEntity.setId(rs.getInt("id"));
                    personnelEntity.setName(rs.getString("name"));
                    personnelEntity.setGender(rs.getString("gender"));
                    personnelEntity.setAge(rs.getInt("age"));
                    personnelEntity.setAddress(rs.getString("address"));
                    personnelEntity.setPhone(rs.getString("phone"));
                    personnelEntity.setPosition(rs.getString("position"));
                    personnelEntityList.add(personnelEntity);
                }
                return personnelEntityList;
            }, null);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return personnelEntities;
    }

    @Override
    public List<PersonnelEntity> getPersonnelList(String name, int currentPageNo, Integer pageSize) {
        List<PersonnelEntity> personnelEntities = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from gym_personnel ");
        List<Object> list = new ArrayList<>();
        if (!StringUtils.isNullOrEmpty(name)){
            sql.append("where name like ?");
            list.add("%"+name+"%");
        }
        //在mysql数据库中，分页使用 limit startIndex，pageSize ; 总数
        sql.append("order by id DESC limit ?,?");
        currentPageNo = (currentPageNo - 1) * pageSize;
        list.add(currentPageNo);
        list.add(pageSize);
        Object[] params = list.toArray();
        System.out.println("sql ----> " + sql.toString());
        try {
            List<PersonnelEntity> personnelEntityList = new ArrayList<>();
            personnelEntities = JdbcUtil.executeQuery(sql.toString(), rs -> {
                while (rs.next()) {
                    PersonnelEntity personnelEntity = new PersonnelEntity();
                    personnelEntity.setId(rs.getInt("id"));
                    personnelEntity.setName(rs.getString("name"));
                    personnelEntity.setGender(rs.getString("gender"));
                    personnelEntity.setAge(rs.getInt("age"));
                    personnelEntity.setPhone(rs.getString("phone"));
                    personnelEntity.setAddress(rs.getString("address"));
                    personnelEntity.setPosition(rs.getString("position"));
                    personnelEntityList.add(personnelEntity);
                }
                return personnelEntityList;
            }, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return personnelEntities;
    }

    @Override
    public Integer[] delPersonnel(Integer[] delId) {
        String sql = "DELETE gym_personnel,gym_salary FROM gym_personnel JOIN gym_salary  ON gym_personnel.id = gym_salary.employee_id WHERE gym_personnel.id = ?";
        Object[] params = delId;
        int[] ints = null;
        try {
           ints = JdbcUtil.batchDelete(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return Arrays.stream(ints).boxed().toArray(Integer[]::new);
    }

    @Override
    public int createPersonnel(PersonnelEntity personnelEntity) {

        String sql = "INSERT INTO gym_personnel (name, gender, age, phone, address, position)\n" +
                "VALUES (?, ?, ?, ?, ?, ?)\n";
        Object[] params = {personnelEntity.getName(),personnelEntity.getGender(),personnelEntity.getAge(),
                           personnelEntity.getPhone(),personnelEntity.getAddress(),personnelEntity.getPosition()};
        int i = 0;
        try {
            i = JdbcUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int updatePersonnel(PersonnelEntity personnelEntity) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE gym_personnel SET");
        if (!StringUtils.isNullOrEmpty(personnelEntity.getName())){
            sb.append(" name = ?,");
            params.add(personnelEntity.getName());
        }
        if (personnelEntity.getAge() >= 0){
            sb.append(" age = ?,");
            params.add(personnelEntity.getAge());
        }
        if (!StringUtils.isNullOrEmpty(personnelEntity.getGender())){
            sb.append(" gender = ?,");
            params.add(personnelEntity.getGender());
        }
        if (!StringUtils.isNullOrEmpty(personnelEntity.getPhone())){
            sb.append(" phone = ?,");
            params.add(personnelEntity.getPhone());
        }
        if (!StringUtils.isNullOrEmpty(personnelEntity.getAddress())){
            sb.append(" address = ?,");
            params.add(personnelEntity.getAddress());
        }
        if (!StringUtils.isNullOrEmpty(personnelEntity.getPosition())){
            sb.append(" position = ?");
            params.add(personnelEntity.getPosition());
        }
        sb.append(" WHERE id = ?");
        params.add(personnelEntity.getId());
        System.out.println("sb:"+sb.toString()+"params:"+params.toString());
        int i = 0;
        try {
            i = JdbcUtil.executeUpdate(sb.toString(),params.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return i;
    }

    @Override
    public PersonnelEntity getByid(String sid) {
        PersonnelEntity entity = null;
        try {
            entity = JdbcUtil.executeQuery("SELECT * FROM gym_personnel WHERE id = ?", rs -> {
                PersonnelEntity personnelEntity = new PersonnelEntity();
                if (rs.next()){
                    personnelEntity.setId(rs.getInt("id"));
                    personnelEntity.setName(rs.getString("name"));
                    personnelEntity.setGender(rs.getString("gender"));
                    personnelEntity.setAge(rs.getInt("age"));
                    personnelEntity.setPhone(rs.getString("phone"));
                    personnelEntity.setAddress(rs.getString("address"));
                    personnelEntity.setPosition(rs.getString("position"));
                }
                return personnelEntity;
            }, sid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return entity;
    }
}
